package com.fcscs.ecms.dao.hibernate;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Hibernate;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fcscs.core.dao.ConnectionManager;
import com.fcscs.core.domain.BaseEntity.DBLoc;
import com.fcscs.cservice.domain.MainMenuItem;

import com.fcscs.cservice.ws.schema.guest.Guest;
import com.fcscs.ecms.dao.ECMSGuestListDao;


@SuppressWarnings("unchecked")
@Repository("guestListDao")
public class ECMSGuestListDaoHibernate implements ECMSGuestListDao {

	
	private static DBLoc TBLoc = new MainMenuItem().getDBLoc();

	@Autowired
	private ConnectionManager connMgr;
	
	@Override
	public List<Guest> getGuestList(long roomnumber, String firstname,
			String lastname) {
		// TODO Auto-generated method stub
		
		String sql = "select fcs_guest.reservation_v.id," +
				"fcs_guest.reservation_v.folio_id," +
				"fcs_guest.reservation_v.org_id," +
				"fcs_guest.reservation_v.property_id," +
				"fcs_guest.reservation_v.guest_profile_id, " +
				"fcs_guest.reservation_v.title," +
				" fcs_guest.reservation_v.guest_firstname ," +
				" fcs_guest.reservation_v.guest_lastname ," +
				" fcs_guest.reservation_v.room_number " +
				"from fcs_guest.reservation_v " +
				"where fcs_guest.reservation_v.room_number=:roomnumber " +
				" and fcs_guest.reservation_v.guest_lastname=:lastname" +
				" and fcs_guest.reservation_v.guest_firstname=:firstname";
		
			/*if(roomnumber != 0 && firstname != null && lastname != null){
				sql = "select fcs_guest.reservation_v.id," +
						"fcs_guest.reservation_v.folio_id," +
						"fcs_guest.reservation_v.org_id," +
						"fcs_guest.reservation_v.property_id," +
						"fcs_guest.reservation_v.title," +
						" fcs_guest.reservation_v.guest_firstname ," +
						" fcs_guest.reservation_v.guest_lastname ," +
						" fcs_guest.reservation_v.room_number " +
						"from fcs_guest.reservation_v " +
						"where fcs_guest.reservation_v.room_number=:roomnumber " +
						" and fcs_guest.reservation_v.guest_lastname=:lastname" +
						" and fcs_guest.reservation_v.guest_firstname=:firstname";
			}
			else if (roomnumber == 0 && firstname != null || lastname != null){
				sql = "select fcs_guest.reservation_v.id," +
						"fcs_guest.reservation_v.folio_id," +
						"fcs_guest.reservation_v.org_id," +
						"fcs_guest.reservation_v.property_id," +
						"fcs_guest.reservation_v.title," +
						" fcs_guest.reservation_v.guest_firstname ," +
						" fcs_guest.reservation_v.guest_lastname ," +
						" fcs_guest.reservation_v.room_number " +
						"from fcs_guest.reservation_v " +
						"where " +
						" fcs_guest.reservation_v.guest_lastname=:lastname" +
						" or fcs_guest.reservation_v.guest_firstname=:firstname";
			}
			 
			else if(roomnumber != 0 && firstname != null && lastname == null){
				sql = "select fcs_guest.reservation_v.id," +
						"fcs_guest.reservation_v.folio_id," +
						"fcs_guest.reservation_v.org_id," +
						"fcs_guest.reservation_v.property_id," +
						"fcs_guest.reservation_v.title," +
						" fcs_guest.reservation_v.guest_firstname ," +
						" fcs_guest.reservation_v.guest_lastname ," +
						" fcs_guest.reservation_v.room_number " +
						"from fcs_guest.reservation_v " +
						"where fcs_guest.reservation_v.room_number=:roomnumber" +
						" and fcs_guest.reservation_v.guest_firstname=:firstname";
			}
			else if(roomnumber != 0 && firstname == null && lastname != null){
				sql = "select fcs_guest.reservation_v.id," +
						"fcs_guest.reservation_v.folio_id," +
						"fcs_guest.reservation_v.org_id," +
						"fcs_guest.reservation_v.property_id," +
						"fcs_guest.reservation_v.title," +
						" fcs_guest.reservation_v.guest_firstname ," +
						" fcs_guest.reservation_v.guest_lastname ," +
						" fcs_guest.reservation_v.room_number " +
						"from fcs_guest.reservation_v " +
						"where fcs_guest.reservation_v.room_number=:roomnumber" +
						" and fcs_guest.reservation_v.guest_lastname=:lastname";
			}*/
			String sql1 = "";
			
			Query sqlQu = connMgr.getCurrentSession(TBLoc).createSQLQuery(sql);
			sqlQu.setParameter("roomnumber", roomnumber);
			sqlQu.setParameter("firstname", firstname);
			sqlQu.setParameter("lastname", lastname);

			List<Guest> guestList = new ArrayList<Guest>();
			List<Object[]> objects = (List<Object[]>)sqlQu.list();
			for(Object[] objTmp : objects){
				Guest guest = new Guest();
				for(int i =0 ; i<objTmp.length ; i++){
					System.out.println(String.valueOf(objTmp[i]));
					
				}
				
				guest.setFolioId(Long.parseLong(String.valueOf(objTmp[1])));
				guest.setReservationId(Long.parseLong(String.valueOf(objTmp[0])));
				guest.setGuestId(Long.parseLong(String.valueOf(objTmp[1])));
				guest.setTitle(String.valueOf(objTmp[5]));
				guest.setFirstname(String.valueOf(objTmp[6]));
				guest.setLastname(String.valueOf(objTmp[7]));
				guest.setRoomnumber(Long.parseLong(String.valueOf(objTmp[8])));
				
				guestList.add(guest);
			}
			
			return guestList;
	}

}
